#-*- coding:utf8 -*-
#-*- author : JiRaiYa -*-
import xlrd, xlwt
from xlutils.copy import copy
import sys
import os

class EXCEL():

    def __init__(self, filepath):
        reload(sys)
        sys.setdefaultencoding('utf8')
        try:
            self.filepath = filepath
            if filepath.endswith('xls') or filepath.endswith('xlsx'):
                xlsfile = xlrd.open_workbook(filepath, formatting_info = True)
                self.xlsfile = xlsfile
            else:
                print 'initial failed, please check %s exists or %s is a excel file'%(filepath, filepath)
        except Exception as ex:
            print 'Initial pyexcel object failed : ', ex

    def dictColNumIndex(self, sheet):
        alpha = ''
        _dict = {}
        for col in xrange(sheet.ncols):
            index = col % 26
            alpha = chr(ord('A') + index)
            if col < 26:
                _dict[col] = alpha
            else:
                rank = int(col / 26)
                _dict[col] = _dict[rank - 1] + alpha
        return _dict

    def dictColNameIndex(self, sheet):
        _dictColNameIndex = {}
        _dict = self.dictColNumIndex(sheet)
        for num in sorted(self.dictColNumIndex(sheet).keys()):
            _dictColNameIndex[_dict[num]] = num
        return _dictColNameIndex

    def getSheet(self, sheetName = None, sheetIndex = 0):
        if sheetName is not None and \
                sheetName in self.xlsfile.sheet_names():
            sheet = self.xlsfile.sheet_by_name(sheetName)
        else:
            if sheetIndex < len(self.xlsfile.sheets()):
                sheetIndex = sheetIndex if sheetIndex != 0 else 0
                sheet = self.xlsfile.sheets()[sheetIndex]
        self.colAlphaIndex = self.dictColNumIndex(sheet)
        self.colNumIndex = self.dictColNumIndex(sheet)
        self.checkDuplicateColName(sheet)
        return sheet

    def getSheetNames(self):
        return self.xlsfile.sheet_names()

    def getHeaders(self, sheet):
        _dict_colName = {}
        for index in xrange(sheet.ncols):
            colName = sheet.cell(0, index).value.strip()
            _dict_colName[colName] = index
        return _dict_colName

    def checkDuplicateColName(self, sheet):
        col = {}
        _check = {}
        colName = sheet.cell(0, 0).value.strip()
        _check[colName] = [0]
        count = 0
        for index in xrange(1, sheet.ncols):
            tempName = sheet.cell(0, index).value.strip()
            if tempName == colName:
                count += 1
                if tempName in _check:
                    _check[tempName].append(index)
                else:
                    _check[tempName] = []
        if count == 0:
            print 'No duplicate cols'
        else:
            for key in _check:
                print 'Found duplicate ColName \'%s\' in col'%(key),_check[key],', index of colName will be the last one'

    def cellRowColName(self, sheet, row, colName):
        col= self.getHeaders(sheet)
        try:
            if col[colName] is not None and row < sheet.nrows:
                self.checkDuplicateColname(sheet)
                return sheet.cell(row, col[colName]).value
            else:
                print 'Please be sure both col name and row index are correct'
        except Exception as ex:
            print ex, colName,'not in this sheet'

    def findCellValueByColName(self, sheet, colName, cellValue):
        dict_cols = self.getHeaders(sheet)
        for row in xrange(1, sheet.nrows):
            if cellValue in str(sheet.cell(row, dict_cols[colName]).value) :
                print '(%d, %s)'%(row + 1, self.colAlphaIndex[dict_cols[colName]])

    def findCellValue(self, sheet, cellValue):
        for row in xrange(1, sheet.nrows):
            for col in xrange(sheet.ncols):
                if cellValue in str(sheet.cell(row, col).value):
                    print 'find [%s] in sheet [%s], row [%d], col [%s]'%(cellValue, sheet.name, row + 1, self.indexAlpha[col])

    def genExcelMarkValue(self, sheet, markValue): # not finished
        fileName = os.path.splitext(self.filepath)
        fileName = fileName[-2] + '_copy' + fileName[-1]
        workbook = xlwt.Workbook(encoding='utf8')
        workbook.add_sheet(sheet.name)
        copyXlsFile = copy(self.xlsfile)
        sheet_write = copyXlsFile.get_sheet(0)
        sheet_write.write(3, 3, 'hello')
        copyXlsFile.save(fileName)

    def getCellValue(self, sheet, row, colName): # get value like human
        colIndex = self.dictColNameIndex(sheet)[colName.upper()]
        cellValue = sheet.cell(row - 1, colIndex).value
        return cellValue
